﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class FilterConfigHeaderDA : IFilterConfigHeaderDA
    {
        private string ALLOW_ORDER_BY = "@ALLOW_ORDER_BY";
        private string CREATED_BY = "@CREATED_BY";
        private string DESCR = "@DESCR";
        private string DO_PATH = "@DO_PATH";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string JOIN_CLAUSE = "@JOIN_CLAUSE";
        private string RECORD_TYPE = "@RECORD_TYPE";
        private string SQL_CHECK_FILTER_CONFIG_HEADER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@FILTER_CONFIG_HEADER WHERE RECORD_TYPE = @RECORD_TYPE ";
        private string SQL_DELETE_FILTER_CONFIG_HEADER = " DELETE FROM @_@FILTER_CONFIG_HEADER WHERE RECORD_TYPE = @RECORD_TYPE ";
        private string SQL_INSERT_FILTER_CONFIG_HEADER = " INSERT INTO @_@FILTER_CONFIG_HEADER ( WH_ID, RECORD_TYPE, DESCR, TABLE_NAME, ALLOW_ORDER_BY, DO_PATH, JOIN_CLAUSE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, SYSTEM_CREATED, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @RECORD_TYPE, @DESCR, @TABLE_NAME, @ALLOW_ORDER_BY, @DO_PATH, @JOIN_CLAUSE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @IS_ACTIVE, @SYSTEM_CREATED, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_FILTER_CONFIG_HEADER = " SELECT  ROWID, WH_ID, RECORD_TYPE, DESCR, TABLE_NAME, ALLOW_ORDER_BY, DO_PATH, JOIN_CLAUSE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, SYSTEM_CREATED, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@FILTER_CONFIG_HEADER ";
        private string SQL_UPDATE_FILTER_CONFIG_HEADER = " UPDATE @_@FILTER_CONFIG_HEADER SET WH_ID = @WH_ID, DESCR = @DESCR, TABLE_NAME = @TABLE_NAME, ALLOW_ORDER_BY = @ALLOW_ORDER_BY, DO_PATH = @DO_PATH, JOIN_CLAUSE = @JOIN_CLAUSE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, IS_ACTIVE = @IS_ACTIVE, SYSTEM_CREATED = @SYSTEM_CREATED, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECORD_TYPE = @RECORD_TYPE  ";
        private string SYSTEM_CREATED = "@SYSTEM_CREATED";
        private string TABLE_NAME = "@TABLE_NAME";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckFilterConfigHeaderIDUnique(string filterConfigHeaderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECORD_TYPE", filterConfigHeaderID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_FILTER_CONFIG_HEADER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckFilterConfigHeaderIDUnique(DataBase dataBase, DbTransaction tran, string filterConfigHeaderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECORD_TYPE", filterConfigHeaderID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_FILTER_CONFIG_HEADER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteFilterConfigHeader(List<string> filterConfigHeaderID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < filterConfigHeaderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECORD_TYPE", filterConfigHeaderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteFilterConfigHeader(DataBase dataBase, DbTransaction tran, List<string> filterConfigHeaderID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < filterConfigHeaderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECORD_TYPE", filterConfigHeaderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllFilterConfigHeader(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllFilterConfigHeader(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public FilterConfigHeaderInfo GetFilterConfigHeaderByID(string filterconfigheaderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID) + " WHERE RECORD_TYPE = @RECORD_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECORD_TYPE", filterconfigheaderID) };
            FilterConfigHeaderInfo filterConfigHeaderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    filterConfigHeaderInfo = new FilterConfigHeaderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECORD_TYPE"].ToString(), reader["DESCR"].ToString(), reader["TABLE_NAME"].ToString(), reader["ALLOW_ORDER_BY"].ToString(), reader["DO_PATH"].ToString(), reader["JOIN_CLAUSE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return filterConfigHeaderInfo;
        }

        public FilterConfigHeaderInfo GetFilterConfigHeaderByID(DataBase dataBase, DbTransaction tran, string filterconfigheaderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID) + " WHERE RECORD_TYPE = @RECORD_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECORD_TYPE", filterconfigheaderID) };
            FilterConfigHeaderInfo filterConfigHeaderInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                filterConfigHeaderInfo = new FilterConfigHeaderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECORD_TYPE"].ToString(), reader["DESCR"].ToString(), reader["TABLE_NAME"].ToString(), reader["ALLOW_ORDER_BY"].ToString(), reader["DO_PATH"].ToString(), reader["JOIN_CLAUSE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return filterConfigHeaderInfo;
        }

        public DataSet GetFilterConfigHeaderByQueryList(List<string> sqlWhere, FilterConfigHeaderQueryEntity filterconfigheaderQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!filterconfigheaderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, filterconfigheaderQuery.CurrentPage, filterconfigheaderQuery.PageSize, filterconfigheaderQuery.SortField, filterconfigheaderQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetFilterConfigHeaderByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, FilterConfigHeaderQueryEntity filterconfigheaderQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!filterconfigheaderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, filterconfigheaderQuery.CurrentPage, filterconfigheaderQuery.PageSize, filterconfigheaderQuery.SortField, filterconfigheaderQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertFilterConfigHeader(FilterConfigHeaderInfo filterConfigHeaderInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    filterConfigHeaderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_FilterConfigHeader_Parameters(filterConfigHeaderInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertFilterConfigHeader(DataBase dataBase, DbTransaction tran, FilterConfigHeaderInfo filterConfigHeaderInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    filterConfigHeaderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_FilterConfigHeader_Parameters(filterConfigHeaderInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_FilterConfigHeader_Parameters(FilterConfigHeaderInfo filterConfigHeaderInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.RECORD_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.DESCR, SqlDbType.VarChar, 50), new SqlParameter(this.TABLE_NAME, SqlDbType.VarChar, 500), new SqlParameter(this.ALLOW_ORDER_BY, SqlDbType.VarChar, 1), new SqlParameter(this.DO_PATH, SqlDbType.VarChar, 500), new SqlParameter(this.JOIN_CLAUSE, SqlDbType.VarChar, 0x7d0), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.SYSTEM_CREATED, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.WhID))
            {
                paramArray[0].Value = filterConfigHeaderInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = filterConfigHeaderInfo.RecordType;
            paramArray[2].Value = filterConfigHeaderInfo.Descr;
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.TableName))
            {
                paramArray[3].Value = filterConfigHeaderInfo.TableName;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.AllowOrderBy))
            {
                paramArray[4].Value = filterConfigHeaderInfo.AllowOrderBy;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.DoPath))
            {
                paramArray[5].Value = filterConfigHeaderInfo.DoPath;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.JoinClause))
            {
                paramArray[6].Value = filterConfigHeaderInfo.JoinClause;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf1))
            {
                paramArray[7].Value = filterConfigHeaderInfo.Udf1;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf2))
            {
                paramArray[8].Value = filterConfigHeaderInfo.Udf2;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf3))
            {
                paramArray[9].Value = filterConfigHeaderInfo.Udf3;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf4))
            {
                paramArray[10].Value = filterConfigHeaderInfo.Udf4;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf5))
            {
                paramArray[11].Value = filterConfigHeaderInfo.Udf5;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf6))
            {
                paramArray[12].Value = filterConfigHeaderInfo.Udf6;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf7))
            {
                paramArray[13].Value = filterConfigHeaderInfo.Udf7;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf8))
            {
                paramArray[14].Value = filterConfigHeaderInfo.Udf8;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf9))
            {
                paramArray[15].Value = filterConfigHeaderInfo.Udf9;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(filterConfigHeaderInfo.Udf10))
            {
                paramArray[0x10].Value = filterConfigHeaderInfo.Udf10;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            paramArray[0x11].Value = filterConfigHeaderInfo.IsActive;
            paramArray[0x12].Value = filterConfigHeaderInfo.SystemCreated;
            paramArray[0x13].Value = filterConfigHeaderInfo.CreatedBy;
            paramArray[20].Value = filterConfigHeaderInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateFilterConfigHeader(FilterConfigHeaderInfo filterConfigHeaderInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    filterConfigHeaderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_FilterConfigHeader_Parameters(filterConfigHeaderInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateFilterConfigHeader(DataBase dataBase, DbTransaction tran, FilterConfigHeaderInfo filterConfigHeaderInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    filterConfigHeaderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_FilterConfigHeader_Parameters(filterConfigHeaderInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_FILTER_CONFIG_HEADER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

